/*DONE* Table [dbo].[<CompanyName>$LSCHT Detailed Revenue Entry$7ecfc871-5d82-43f1-9c54-59685e82318d]   Script Date: 21.7.2022 - GKO ******/
/*Add prestaging tables for all companies in LSInsight$Companies table*/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT
		MAX([Company])
	FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT
		LSInsight$Companies.[CompanyPrefix]
	FROM dbo.LSInsight$Companies
	WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$LSCHT Detailed Revenue Entry$7ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$LSCHT Detailed Revenue Entry$7ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Entry No_] [int] IDENTITY(1,1) NOT NULL,
	[Reservation No_] [nvarchar](20) NOT NULL,
	[Paying Reservation No_] [nvarchar](20) NOT NULL,
	[Group Res No_] [nvarchar](20) NOT NULL,
	[Quantity] [decimal](38, 20) NOT NULL,
	[Unit Price] [decimal](38, 20) NOT NULL,
	[Line Discount _] [decimal](38, 20) NOT NULL,
	[Line Discount Amount] [decimal](38, 20) NOT NULL,
	[Amount] [decimal](38, 20) NOT NULL,
	[Amount incl_ VAT] [decimal](38, 20) NOT NULL,
	[VAT Amount] [decimal](38, 20) NOT NULL,
	[Amount (LCY)] [decimal](38, 20) NOT NULL,
	[Amount incl_ VAT (LCY)] [decimal](38, 20) NOT NULL,
	[VAT Amount (LCY)] [decimal](38, 20) NOT NULL,
	[Currency] [nvarchar](10) NOT NULL,
	[Currency Factor] [decimal](38, 20) NOT NULL,
	[Room No_] [nvarchar](20) NOT NULL,
	[Posted to Finance] [tinyint] NOT NULL,
	[Date] [datetime] NOT NULL,
	[Date Posted to Finance] [datetime] NOT NULL,
	[Item No_] [nvarchar](20) NOT NULL,
	[Rate Code] [nvarchar](20) NOT NULL,
	[Package Code] [nvarchar](20) NOT NULL,
	[Revenue Type] [int] NOT NULL,
	[Invoice No_] [int] NOT NULL,
	[Customer No_] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[VAT Identifier] [nvarchar](20) NOT NULL,
	[VAT _] [decimal](38, 20) NOT NULL,
	[Invoiced] [tinyint] NOT NULL,
	[Sales Invoice No_] [nvarchar](20) NOT NULL,
	[Receipt No_] [nvarchar](20) NOT NULL,
	[POS Trans_ Line No_] [int] NOT NULL,
	[Transaction Type] [int] NOT NULL,
	[Store No_] [nvarchar](20) NOT NULL,
	[POS Terminal No_] [nvarchar](20) NOT NULL,
	[Room Charge] [tinyint] NOT NULL,
	[Room Charge Receipt No_] [nvarchar](20) NOT NULL,
	[Room Charge Line No_] [int] NOT NULL,
	[AccommodationTax] [decimal](38, 20) NOT NULL,
	[Property] [nvarchar](20) NOT NULL,
	[Source Table] [int] NOT NULL,
	[Source Table Entry No_] [int] NOT NULL,
	[Price Includes VAT] [tinyint] NOT NULL,
	[Revenue Code] [nvarchar](20) NOT NULL,
	[Adults] [int] NOT NULL,
	[Children] [int] NOT NULL,
	[DateTimeCreated] [datetime] NOT NULL,
	[Room Type] [nvarchar](20) NOT NULL,
	[Unit Price Changed] [tinyint] NOT NULL,
	[Included in Package] [tinyint] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
	[Included in Rate] [tinyint] NOT NULL,
	[DRR Guid] [uniqueidentifier] NOT NULL,
	[Activity No_] [nvarchar](20) NOT NULL,
	[Is Manual Line Discount _] [tinyint] NOT NULL,
	[Unit of Measure] [nvarchar](20) NOT NULL,
	[Variant] [nvarchar](20) NOT NULL,
	[Activity Location] [nvarchar](20) NOT NULL,
	[Activity Internal Status] [int] NOT NULL,
	[Activity Status] [nvarchar](20) NOT NULL,
	[Posting Pattern] [int] NOT NULL,
	[Source Table Entry2 No_] [int] NOT NULL,
	[Room Rate Sum] [decimal](38, 20) NOT NULL,
	[Sales Header No_] [nvarchar](20) NOT NULL,
	[Trace] [nvarchar](100) NOT NULL,
	[Reverse of Entry No_] [int] NOT NULL,
	[Sales Line No_] [int] NOT NULL,
	[Paid] [tinyint] NOT NULL,
	[Extra Adult] [decimal](38, 20) NOT NULL,
	[Deactivated] [tinyint] NOT NULL,
 CONSTRAINT [' + @Company + '$LSCHT Detailed Revenue Entry$7ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Entry No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$LSCHT Detailed Revenue Entry$7ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i + 1
END
